package cn.edu.sysu.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.edu.sysu.dto.SolutionDto;
import cn.edu.sysu.util.Transaction;

public enum SolutionDao {

	INSTANCE;

	private static String GET_NEXT_REVISION = "select max(revision)+1 from cws_solution where problem_id=? and user_id=?";

	public int getNextRevisionNumber(Transaction transaction, SolutionDto solutionDto) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_NEXT_REVISION);
			ps.setInt(1, solutionDto.getProblem_id());
			ps.setInt(2, solutionDto.getUser_id());

			rs = ps.executeQuery();

			while (rs.next()) {
				return rs.getInt(1);
			}
			return 1;

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

		return -1;

	}

	private SolutionDto makeSolutionDto(Transaction transaction, ResultSet rs) throws SQLException {
		int problem_id = rs.getInt(1);
		int user_id = rs.getInt(2);
		int revision = rs.getInt(3);
		Timestamp post_time = rs.getTimestamp(4);
		String code = rs.getString(5);
		int correct = rs.getInt(6);

		//UserDto u = new UserDto(user_id, team_id, user_name, email, deleted);
		SolutionDto s = new SolutionDto(code, post_time, problem_id, revision, user_id, correct);

		return s;
	}

	private static final String INSERT_SOLUTION = "insert into cws_solution (problem_id, user_id, revision, post_time, code, correct) values (?,?,?,?,?,?)";

	public void saveSolution(Transaction transaction, SolutionDto sd) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(INSERT_SOLUTION);
			ps.setInt(1, sd.getProblem_id());
			ps.setInt(2, sd.getUser_id());
			ps.setInt(3, sd.getRevision());
			ps.setTimestamp(4, new Timestamp(sd.getPost_time().getTime()));
			ps.setString(5, sd.getCode());
			ps.setInt(6, 99);

			ps.executeUpdate();
			transaction.commit();

			System.out.println("finish insert a solution ");

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

	}

	private static final String GET_LATEST_SOL = "SELECT	cs.*, cu.*FROM	cws_solution cs,	(		SELECT		problem_id,	user_id,			max(revision) AS r	FROM			cws_solution where problem_id=?		GROUP BY			problem_id,			user_id	) t,	cws_user cu WHERE	cs.problem_id = t.problem_id AND cs.user_id = t.user_id AND cs.revision = t.r and cs.user_id = cu.user_id";

	public Map<Integer, SolutionDto> getLatestSolution(Transaction transaction, int problem_id) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Map<Integer, SolutionDto> map = new HashMap<Integer, SolutionDto>();
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_LATEST_SOL);
			ps.setInt(1, problem_id);
			rs = ps.executeQuery();
			while (rs.next()) {
				SolutionDto solution = makeSolutionDto(transaction, rs);
				map.put(solution.getUser_id(), solution);
			}
			return map;

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}
		return map;
	}

	private static final String GET_SOLUTION_BY_USER_PROBLEM_REV = "select * from cws_solution where user_id = ? and problem_id =? and revision = ?";

	public SolutionDto getSolution(Transaction transaction, int problemId, int userId, int revision) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Map<Integer, SolutionDto> map = new HashMap<Integer, SolutionDto>();
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_SOLUTION_BY_USER_PROBLEM_REV);
			ps.setInt(1, userId);
			ps.setInt(2, problemId);
			ps.setInt(3, revision);
			rs = ps.executeQuery();
			while (rs.next()) {
				SolutionDto solution = makeSolutionDto(transaction, rs);
				return solution;
			}
			return new SolutionDto("", new Date(System.currentTimeMillis()), problemId, revision, userId, 99);

		} catch (SQLException se) {

			se.printStackTrace();
			return new SolutionDto("", new Date(System.currentTimeMillis()), problemId, revision, userId, 99);

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

	}

	private static final String GET_ALL_SOLUTION_BY_PROBLEM = "select * from cws_solution where problem_id =? order by user_id, revision desc";

	public List<SolutionDto> getAllSolutionsForProblem(Transaction transaction, int problem_id) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<SolutionDto> list = new ArrayList<SolutionDto>();
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_ALL_SOLUTION_BY_PROBLEM);
			ps.setInt(1, problem_id);
			rs = ps.executeQuery();
			while (rs.next()) {
				SolutionDto solution = makeSolutionDto(transaction, rs);
				list.add(solution);
			}
			return list;

		} catch (SQLException se) {

			se.printStackTrace();
			return new ArrayList<SolutionDto>();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}
	}

	private static final String UPDATE_CORRECT = "update cws_solution set correct = ? where user_id = ? and problem_id = ? and revision = ?";

	public void updateCorrectness(Transaction transaction, int solution_user_id, int problem_id, int revision,
			int correct) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(UPDATE_CORRECT);
			ps.setInt(1, correct);
			ps.setInt(2, solution_user_id);
			ps.setInt(3, problem_id);
			ps.setInt(4, revision);

			ps.executeUpdate();
			transaction.commit();

			System.out.println("finish insert a solution ");

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

	}

	private static final String GET_ALL_SUBMITTED_SOLUTION = "select problem_id, user_id,max(correct) from app_cwsalgorithm.cws_solution where problem_id = ? and correct<99 group by problem_id, user_id";

	public List<SolutionDto> getSubmittedSolution(Transaction transaction, int problemId) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<SolutionDto> list = new ArrayList<SolutionDto>();
		try {
			transaction.begin();

			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_ALL_SUBMITTED_SOLUTION);
			ps.setInt(1, problemId);
			rs = ps.executeQuery();
			while (rs.next()) {
				int problem_id = rs.getInt(1);
				int user_id = rs.getInt(2);
				int correct = rs.getInt(3);
				SolutionDto solution = new SolutionDto("", null, problem_id, -1, user_id, correct);
				list.add(solution);
			}
			return list;

		} catch (SQLException se) {

			se.printStackTrace();
			return new ArrayList<SolutionDto>();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}
	}
}
